﻿CREATE PROCEDURE [dbo].[LeaseSummaryReport]
	@BeginDate datetime, 
	@EndDate datetime,
	@ProjectId int
WITH RECOMPILE 
AS
BEGIN
	SET @EndDate = DateAdd(day, 1, @EndDate);
	WITH 
	[EntranceView]
	AS
	(
		SELECT	[Id],
						[BuildingId]
		FROM		[dbo].[Entrance]
		WHERE	[IsDeleted] = 0
	),
	[FloorView]
	AS
	(
		SELECT	[Id],
						[BuildingId]
		FROM		[dbo].[Floor]
		WHERE	[IsDeleted] = 0
	),
	[BuildingView]
	AS
	(
		SELECT	[Id],
						[ProjectId]
		FROM		[dbo].[Building]
		WHERE	[IsDeleted] = 0
	),
	[ProjectView]
	AS
	(
		SELECT	[Id], 
						[Name]
		FROM		[dbo].[Project]
		WHERE	[IsDeleted] = 0 AND [Id] = @ProjectId
	),
	[PropertyView]
	AS
	(
		SELECT	R.[Id],
						F.[Id] AS [FloorId],
						E.[Id] AS [EntranceId],
						B.[Id] AS [BuildingId],
						P.[Id] AS [ProjectId], 
						R.[LeaseArea], 
						R.[ActiveLeaseAgreementId]
		FROM		[dbo].[Property] AS R 
			INNER JOIN [EntranceView] AS E ON R.[EntranceId] = E.[Id]
			INNER JOIN [FloorView] AS F ON R.[FloorId] = F.[Id]
			INNER JOIN [BuildingView] AS B ON E.[BuildingId] = B.[Id]
			INNER JOIN [ProjectView] AS P ON B.[ProjectId] = P.[Id]
		WHERE R.[IsDeleted] = 0
	),
	[ActiveLeaseAgreementView]
	AS
	(
		SELECT	AG.[Id],
						AG.[PropertyId],
						AG.[SalesEmployeeId], 
						AG.[CustomerId], 
						R.[LeaseArea]
		FROM		[dbo].[LeaseAgreement] AS AG
			INNER JOIN [PropertyView] AS R ON AG.[PropertyId] = R.[Id] AND AG.[Id] = R.[ActiveLeaseAgreementId] 
		WHERE	AG.[IsDeleted] = 0
	),
	[LeaseAgreementView]
	AS
	(
		SELECT	AG.[Id],
						AG.[PropertyId],
						AG.[SalesEmployeeId], 
						AG.[CustomerId], 
						R.[LeaseArea]
		FROM		[dbo].[LeaseAgreement] AS AG
			INNER JOIN [PropertyView] AS R ON AG.[PropertyId] = R.[Id]
		WHERE	AG.[IsDeleted] = 0
	),
	[LeaseOrderView]
	AS
	(
		SELECT	LO.[Id], 
						LO.[LeaseAgreementId]
		FROM		[dbo].[LeaseOrder] AS LO
			INNER JOIN [LeaseAgreementView] AS AG ON LO.[LeaseAgreementId] = AG.[Id]
		WHERE	LO.[IsDeleted] = 0 AND LO.[SignatureDate] >= @BeginDate AND LO.[SignatureDate] < @EndDate
	),
	[LeaseContractView]
	AS
	(
		SELECT	C.[Id], 
						C.[LeaseAgreementId],
						AG.[LeaseArea], 
						C.[TotalAmount],
						ISNULL(CR.[ReceivedAmount], 0) AS [ReceivedAmount]
		FROM		[dbo].[LeaseContract] AS C
			INNER JOIN [LeaseAgreementView] AS AG ON C.[LeaseAgreementId] = AG.[Id]
			LEFT OUTER JOIN
			(
				SELECT	[LeaseContractId],
								SUM([ReceivedAmount]) AS [ReceivedAmount]
				FROM		[dbo].[LeaseContractReceipt]
				WHERE	[IsDeleted] = 0 AND [ReceivedDate] >= @BeginDate AND [ReceivedDate] < @EndDate
				GROUP BY	
								[LeaseContractId]
			) AS CR
			ON C.[Id] = CR.[LeaseContractId]
		WHERE	C.[IsDeleted] = 0 AND C.[SignatureDate] >= @BeginDate AND C.[SignatureDate] < @EndDate
	),
	[LeaseCancellationView]
	AS
	(
		SELECT	CL.[Id], 
						CL.[LeaseAgreementId]
		FROM		[dbo].[LeaseCancellation] AS CL
			INNER JOIN [LeaseAgreementView] AS AG ON CL.[LeaseAgreementId] = AG.[Id]
		WHERE	CL.[IsDeleted] = 0 AND CL.[CancellationDate] >= @BeginDate AND CL.[CancellationDate] < @EndDate
	),
	[LeaseAgreementDetailView]
	AS
	(
		SELECT	AG.[Id] AS [LeaseAgreementId], 
						LO.[Id] AS [LeaseOrderId],
						C.[Id] AS [LeaseContractId]
		FROM		[LeaseAgreementView] AS AG
			LEFT OUTER JOIN [LeaseOrderView] AS LO ON AG.[Id] = LO.[LeaseAgreementId]
			LEFT OUTER JOIN [LeaseContractView] AS C ON AG.[Id] = C.[LeaseAgreementId]
	),
	[LeaseOrderAllView]
	AS
	(
		SELECT	LO.[Id], 
						LO.[LeaseAgreementId]
		FROM		[dbo].[LeaseOrder] AS LO
			INNER JOIN [LeaseAgreementView] AS AG ON LO.[LeaseAgreementId] = AG.[Id]
		WHERE	LO.[IsDeleted] = 0 AND LO.[SignatureDate] < @EndDate
	),
	[LeaseContractAllView]
	AS
	(
		SELECT	C.[Id], 
						C.[LeaseAgreementId],
						AG.[LeaseArea], 
						C.[TotalAmount],
						ISNULL(CR.[ReceivedAmount], 0) AS [ReceivedAmount]
		FROM		[dbo].[LeaseContract] AS C
			INNER JOIN [LeaseAgreementView] AS AG ON C.[LeaseAgreementId] = AG.[Id]
			LEFT OUTER JOIN
			(
				SELECT	[LeaseContractId],
								SUM([ReceivedAmount]) AS [ReceivedAmount]
				FROM		[dbo].[LeaseContractReceipt]
				WHERE	[IsDeleted] = 0 AND [ReceivedDate] < @EndDate
				GROUP BY	
								[LeaseContractId]
			) AS CR
			ON C.[Id] = CR.[LeaseContractId]
		WHERE	C.[IsDeleted] = 0 AND C.[SignatureDate] < @EndDate
	),
	[LeaseCancellationAllView]
	AS
	(
		SELECT	CL.[Id], 
						CL.[LeaseAgreementId]
		FROM		[dbo].[LeaseCancellation] AS CL
			INNER JOIN [LeaseAgreementView] AS AG ON CL.[LeaseAgreementId] = AG.[Id]
		WHERE	CL.[IsDeleted] = 0 AND CL.[CancellationDate] < @EndDate
	),
	[LeaseAgreementDetailAllView]
	AS
	(
		SELECT	AG.[Id] AS [LeaseAgreementId], 
						LO.[Id] AS [LeaseOrderId],
						C.[Id] AS [LeaseContractId]
		FROM		[LeaseAgreementView] AS AG
			LEFT OUTER JOIN [LeaseOrderAllView] AS LO ON AG.[Id] = LO.[LeaseAgreementId]
			LEFT OUTER JOIN [LeaseContractAllView] AS C ON AG.[Id] = C.[LeaseAgreementId]
	)
	SELECT	(SELECT COUNT(*) FROM [LeaseAgreementDetailView] AS AGD INNER JOIN [ActiveLeaseAgreementView] AS AG ON AGD.[LeaseAgreementId] = AG.[Id] WHERE AGD.[LeaseOrderId] IS NOT NULL AND AGD.[LeaseContractId] IS NULL) AS [LeaseOrderCount],
					(SELECT COUNT(*) FROM [LeaseAgreementDetailView] AS AGD INNER JOIN [ActiveLeaseAgreementView] AS AG ON AGD.[LeaseAgreementId] = AG.[Id] WHERE  AGD.[LeaseContractId] IS NOT NULL) AS [LeaseContractCount],
					(SELECT COUNT(*) FROM [LeaseCancellationView] AS CL) AS [LeaseCancellationCount],

					(SELECT SUM(C.[LeaseArea]) FROM [LeaseContractView] AS C INNER JOIN [ActiveLeaseAgreementView] AS AG ON C.[LeaseAgreementId] = AG.[Id]) AS [LeaseArea], 
					(SELECT SUM(C.[TotalAmount]) FROM [LeaseContractView] AS C INNER JOIN [ActiveLeaseAgreementView] AS AG ON C.[LeaseAgreementId] = AG.[Id]) AS [TotalAmount], 
					(SELECT SUM(C.[ReceivedAmount]) FROM [LeaseContractView] AS C INNER JOIN [ActiveLeaseAgreementView] AS AG ON C.[LeaseAgreementId] = AG.[Id]) AS [ReceivedAmount], 

					(SELECT COUNT(*) FROM [LeaseAgreementDetailAllView] AS AGD INNER JOIN [ActiveLeaseAgreementView] AS AG ON AGD.[LeaseAgreementId] = AG.[Id] WHERE AGD.[LeaseOrderId] IS NOT NULL AND AGD.[LeaseContractId] IS NULL) AS [AllLeaseOrderCount],
					(SELECT COUNT(*) FROM [LeaseAgreementDetailAllView] AS AGD INNER JOIN [ActiveLeaseAgreementView] AS AG ON AGD.[LeaseAgreementId] = AG.[Id] WHERE AGD.[LeaseContractId] IS NOT NULL) AS [AllLeaseContractCount],
					(SELECT COUNT(*) FROM [LeaseCancellationAllView] AS CL) AS [AllLeaseCancellationCount],

					(SELECT SUM(C.[LeaseArea]) FROM [LeaseContractAllView] AS C INNER JOIN [ActiveLeaseAgreementView] AS AG ON C.[LeaseAgreementId] = AG.[Id]) AS [AllLeaseArea], 
					(SELECT SUM(C.[TotalAmount]) FROM [LeaseContractAllView] AS C INNER JOIN [ActiveLeaseAgreementView] AS AG ON C.[LeaseAgreementId] = AG.[Id]) AS [AllTotalAmount], 
					(SELECT SUM(C.[ReceivedAmount]) FROM [LeaseContractAllView] AS C INNER JOIN [ActiveLeaseAgreementView] AS AG ON C.[LeaseAgreementId] = AG.[Id]) AS [AllReceivedAmount], 

					CASE WHEN (SELECT COUNT(*) FROM [LeaseAgreementView] AS AG) = 0 THEN 0 ELSE (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [LeaseCancellationView] AS CL) / (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [LeaseAgreementView] AS AG) END AS [AllLeaseCancellationLeaseAgreementRatio]
END
